* 1) Open repeat sales in Land Reg (matched with HTB) ----

use "${datadir}\landreg_2013-2020_matched_big", replace

bysort unit_id (dated transaction_id): gen nextyear = year[_n+1]
gen htb = !mi(schemeid)




* 2) Add in redemptions data ----

merge m:1 schemeid using ///
	"${datadir}\HCA_redemptions_all", keep(1 3) ///
	keepusing(Datepurchased HCACash OriginalHCAshare Transactiontype ///
	Salepriceagree Completion merge_htb_main) gen(merge_repaid)

gen stair = regexm(Transaction, "staircasing") 



* 3) Merge in appreciation data ----

// Match the local authorities
merge m:1 pcd using ///
	"${datadir}\la_region_postcode_lookup_2018", ///
	keep(1 3) nogen
ren localauthoritycode areacode 


// Index corresponding to datem

merge m:1 datem areacode using /// 
	"${datadir}\local_price_appreciation_2020", ///
	keep(1 3) nogen keepusing(index)
ren index index_datem


// Index corresponding to staircasing
ren datem datem_lr

gen datem = mofd(Completion)

merge m:1 datem areacode using /// regionname
	"${datadir}\local_price_appreciation_2020", ///
	keep(1 3) nogen keepusing(index)
ren index index_completion
ren datem datem_completion





* 3) Which staircased properties have a sale afterwards? ----

bysort unit_id (dated transaction_id): gen stair_sold = !mi(year[_n+1]) & ///
	stair & datem_completion < datem_lr[_n+1]


// Annualised
bysort unit_id (dated transaction_id): gen appr = index_datem[_n+1]/index_completion 

bysort unit_id (dated transaction_id): gen diff_stair_sold = dated[_n+1] - Completion if stair_sold
gen diff_stair_sold_a = diff_stair_sold / 365
gen appr_a = (appr^(1/diff_stair_sold_a) - 1) * 100

sum appr_a if stair_sold 
sum appr_a if diff_stair_sold_a > 0.5 & stair_sold

sum appr_a if diff_stair_sold_a < 1/2 & stair_sold




* 4) Which staircased properties have been valued the same as the purchase price? ----


gen zeroval = Salepriceagree == price & stair

sum appr_a if stair_sold & zeroval
sum appr_a if diff_stair_sold_a > 0.5 & stair_sold & zeroval

sum appr_a if diff_stair_sold_a < 1/2 & stair_sold & zeroval
